It seems that Microsoft Great Plains is getting more and more popular, in part due to the Microsoft muscles behind it. It is now aimed at the full spectrum of customers in the horizontal and vertical market. Small businesses use Small Business Manager (which is based on the same technology: Great Plains Dexterity dictionary and runtime), Great Plains Standard on MSDE is for small and medium customers, and then Great Plains caters to the rest of the market until the big corporations. There are various reporting tools available and you definitely need to know which one to use for the different types of reports.
If you are the database administrator who is asked to import some data into Great Plains or repair or copy data from one company to another, read this and you will have the clues on where to look for more.
1. Microsoft Great Plains Table Structure: Start Great Plains and go to Tools->Resource Description->Tables. Find the table in the appropriate series. If you are looking for customers, it should be RM00101, customer master file.
2.DEX_ROW_ID. This is the identity column and every Great Plains table has it; this is due to the Great Plains Dexterity technology. This column is never used as a key field, so don’t try to link your tables on DEX_ROW_ID. In case you need to transfer the table from one company to another, you should use these queries:
Select * in GL00100_BAK from TWO.dbo.GL00100
Go
alter table GL00100_BAK drop column DEX_ROW_ID
Go
insert into GL00100 select * from GL00100_BAK
Go
drop down table GL00100_BAK
The above set of queries will transfer GL00100 (master account table) from TWO companies to your current company. You then need to run Checklinks (see the GP Manual) to re-create the rest of the account master related tables.
3. Do not modify the table; sometimes it looks good if you just add a couple of extra columns to the table, like in IV00101, inventory master file, why not add a couple of extra descriptions? If you do this, the Great Plains Dexterity engine will not be able to read all of your elements, due to the fact that DYNAMICS.DIC (main file of the Great Plains dictionary) has an exact description of all the tables and Dexterity uses it to read and write to the table. specific table.
4. Feel free to create SQL views or stored procs. If you’re helping your internal developers create Crystal Reports, good SQL views are a big help for them. Let me give you an example, the view below will show historical SOP labor and invoices; then Crystal can use it.
Create SOP_WORK_HIST view
ace
select SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP30200 where SOPTYPE=3 and VOIDSTTS=0
Union
select SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP10100 where SOPTYPE=3 and VOIDSTTS=0
The above view will show all work and historical invoices not voided (SOP Type = 3 stays per invoice)
5. Some repair/unlock tips:
If you run the above query against the DYNAMICS database, it will unlock the user, who accidentally shut down the computer without logging out of Great Plains:
remove ACTIVITY where USERID=’JOHN’
The following will unlock the pendant lot:
update SY00500 set BCHSTTUS = 0 where BACHNUMB = ‘JULYINVOICES04’
Happy consulting! If you want us to do the work, call us 1-866-528-0577! [email protected]